package com.aigc.sclassroom.mapper;

import com.aigc.sclassroom.pojo.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface HomeworkMapper {
    @Select("select * from homework where course_manage_id = #{courseManageId}")
    List<Homework> selectHomeworkAll(Long courseManageId);

    @Select("select question from question_answer where homework_id=#{homeworkId}")
    String[] selectQuestionByHomeworkId(Long homeworkId);

    @Select("select question_id from question_answer where homework_id=#{homeworkId}")
    Long[] selectQuestionIdByHomeworkId(Long homeworkId);

    @Select("select answer from question_answer where homework_id=#{homeworkId}")
    String[] selectAnswerByHomeworkId(Long homeworkId);

    @Select("select score_set from question_answer where homework_id=#{homeworkId}")
    int[] selectScoreSetByHomeworkId(Long homeworkId);


    @Select("select sum(g.score)\n" +
            "from student_answer sa\n" +
            "left join grade g on sa.student_answer_id = g.student_answer_id\n" +
            "where sa.homework_id = #{homeworkId} and sa.user_id = #{userId};")
    Integer selectTotalScore(Long homeworkId,Long userId);
    @Insert("insert into homework(homework_name,course_manage_id,create_time,deadline)" +
            "values (#{homeworkName},#{courseManageId},now(),#{deadline})")
    void addHomework(Homework homework);

    @Insert("insert into question_answer(homework_id,question,answer,score_set)" +
            "values (#{homeworkId},#{question},#{answer},#{scoreSet})")
    void addQuestion(String question,String answer,int scoreSet, Long homeworkId);

    @Select("SELECT\n" +
            "    sa.user_id,\n" +
            "    s.user_name,\n" +
            "    MAX(sa.create_time) AS submit_time,\n" +
            "    IFNULL(SUM(g.score), -1) AS total_score  -- 计算每个学生的作业总分\n" +
            "FROM\n" +
            "    student_answer sa\n" +
            "JOIN\n" +
            "    sys_user s ON sa.user_id = s.user_id\n" +
            "LEFT JOIN\n" +
            "    grade g ON sa.student_answer_id = g.student_answer_id\n" +
            "WHERE\n" +
            "    sa.homework_id = #{homeworkId}  -- 替换为具体的作业ID\n" +
            "GROUP BY\n" +
            "    sa.user_id")
    List<HomeworkResult> selectResult(Long homeworkId);

    @Select("select sa.student_answer_id,qa.question_id,qa.question,qa.score_set,sa.answer,qa.answer as standard_answer,score,comment\n" +
            "from student_answer sa\n" +
            "left join grade g on sa.student_answer_id = g.student_answer_id\n" +
            "left join question_answer qa on qa.question_id = sa.question_id\n" +
            "where sa.homework_id = #{homeworkId} and user_id = #{userId};")
    List<StuAnswer> selectStuAnswer(Long homeworkId,Long userId);

    @Update("update grade set score =#{score},comment =#{comment}\n" +
            "where student_answer_id =#{studentAnswerId}")
    void updateScore(StuAnswer stuAnswer);

    //查询班级人数
    @Select("select count(team_id)\n" +
            "from team t\n" +
            "left join course_manage cm on t.course_manage_id = cm.course_manage_id\n" +
            "where t.course_manage_id=#{courseManageId}")
    Integer selectSum(Long courseManageId);

    //查询某次作业的提交人数
    @Select("SELECT\n" +
            "    COUNT(DISTINCT sa.user_id) AS submit_count\n" +
            "FROM\n" +
            "    student_answer sa\n" +
            "WHERE\n" +
            "    sa.homework_id = #{homeworkId}")
    Integer selectSubmitCount(Long homeworkId);

    @Insert("insert into student_answer(question_id,homework_id,user_id,answer,create_time)\n" +
            "values (#{questionId},#{homeworkId},#{userId},#{answer},now())")
    void insertAnswer(Answer answer);


    @Select("select student_answer_id\n" +
            "from student_answer\n" +
            "where question_id =#{questionId} and homework_id = #{homeworkId} and user_id = #{userId}")
    Long selectStudentAnswerId(Answer answer);

    @Select("select student_answer_id from student_answer where user_id =#{userId} and homework_id =#{homeworkId}")
    List<Long> selectId(Long userId,Long homeworkId);
    @Insert("insert into grade(student_answer_id,create_time)" +
            "values (#{studentAnswerId},now())")
    void insertGrade(Long studentAnswerId);

    @Select("select score from grade where student_answer_id=#{studentAnswerId}")
    Integer selectScore(Long studentAnswerId);

    @Select("SELECT\n" +
            "    COUNT(DISTINCT sa.user_id) AS user_count,\n" +
            "    SUM(g.score) AS average_score\n" +
            "FROM\n" +
            "    student_answer sa\n" +
            "LEFT JOIN\n" +
            "    grade g ON sa.student_answer_id = g.student_answer_id\n" +
            "WHERE\n" +
            "    sa.homework_id = #{homeworkId}\n" +
            "    AND g.score IS NOT NULL")
    AverageCount selectAverage(Long homeworkId);

    @Update("UPDATE homework SET state = #{state} WHERE deadline <= #{now} AND state != '已截止'")
    void updateExpiredHomeworkStatus(LocalDateTime now, String state);
}
